package com.hunan.yllxy.util;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.read.builder.ExcelReaderBuilder;
import com.alibaba.excel.read.builder.ExcelReaderSheetBuilder;
import com.alibaba.excel.read.metadata.holder.ReadWorkbookHolder;
import com.alibaba.excel.read.metadata.holder.csv.CsvReadWorkbookHolder;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.hunan.yllxy.vo.ExcelTitleVo;
import org.apache.commons.io.FileUtils;
import org.apache.logging.log4j.util.Strings;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.HashMap;
import java.util.List;
import java.util.UUID;

/**
 * @Author: 刘华
 * @Date: 2024/5/16 下午6:23
 * @Version 1.0
 */
@Component
public class ExcelUtils {


    /**
     * 从指定位置获取文件后进行导入
     *
     * @param filePath   文件路径
     * @param titleRows  表格标题行数,默认0
     * @param headerRows 表头行数,默认1
     * @param pojoClass  上传后需要转化的对象
     * @return
     */
    public <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass)
            throws Exception {
        if (Strings.isEmpty(filePath)) {
            return null;
        } else {
            final ImportParams params = new ImportParams();
            // 表格标题行数,默认0
            params.setTitleRows(titleRows);
            // 表头行数,默认1
            params.setHeadRows(headerRows);
            // 是否需要保存上传的Excel
            params.setNeedSave(true);
            // 保存上传的Excel目录
            params.setSaveUrl("/excel/");
            return ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
        }
    }

    /**
     * 上传文件导入
     *
     * @param file
     * @param titleRows  标题行
     * @param headerRows 表头行
     * @param needVerfiy 是否检验excel内容
     * @param pojoClass  导入的对象
     * @return
     * @throws Exception List<T>
     */
    public <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, boolean needVerfiy,
                                   Class<T> pojoClass) throws Exception {
        if (file == null) {
            return null;
        }
        String originalFilename = file.getOriginalFilename();
        String substring = originalFilename.substring(originalFilename.lastIndexOf(".") + 1);
        if (substring.equalsIgnoreCase("xlsx") || substring.equalsIgnoreCase("xls")) {
            return baseImport(file.getInputStream(), titleRows, headerRows, needVerfiy, pojoClass);
        } else if (substring.equalsIgnoreCase("csv")) {
            File excelFile = null;
            String excelFileName = null;
            String tempPath = null;
            InputStream inputStream = null;
            try {
                tempPath = TempPathUtil.getTempPath();
                String fileUuid = UUID.randomUUID().toString();
                excelFileName = tempPath + "\\" + fileUuid + ".xls";
                excelFile = new File(excelFileName);
                if (!excelFile.exists()) {
                    excelFile.createNewFile();
                }
                convertCsvToExcel(file.getInputStream(), excelFileName);
                inputStream = new FileInputStream(excelFileName);
                return baseImport(inputStream, titleRows, headerRows, needVerfiy, pojoClass);
            } catch (Exception e) {
                throw new RuntimeException(e);
            } finally {
                try {
                    inputStream.close();
                } catch (Exception e) {
                    throw new RuntimeException(e);
                }
                try {
                    FileUtils.deleteDirectory(new File(tempPath));
                } catch (Exception e) {
                    throw new RuntimeException(e);
                }
            }
        } else {
            return null;
        }

    }

    public void convertCsvToExcel(InputStream inputStream, String xlsxFile) {
        ExcelReader excelReader = null;
        try {
            //使用EasyExcel读取csv文件
            ExcelReaderBuilder readerBuilder = EasyExcel.read(inputStream);
            readerBuilder.headRowNumber(0);
            readerBuilder.excelType(ExcelTypeEnum.CSV);
            excelReader = readerBuilder.build();
            ReadWorkbookHolder readWorkbookHolder = excelReader.analysisContext().readWorkbookHolder();
            if (readWorkbookHolder instanceof CsvReadWorkbookHolder) {
                CsvReadWorkbookHolder csvReadWorkbookHolder = (CsvReadWorkbookHolder) readWorkbookHolder;
                // 底层读取csv 用的是apache的common-csv 所以设置 CsvFormat即可
                csvReadWorkbookHolder.setCsvFormat(csvReadWorkbookHolder.getCsvFormat().withDelimiter(',').withQuote(null));
            }

            List<Object> dataList = new ExcelReaderSheetBuilder(excelReader).doReadSync();
            /**
             * 解决标题特殊字符
             */
            HashMap hashMap = (HashMap) dataList.get(0);
            for (Object o : hashMap.keySet()) {
                Object value = hashMap.get(o);
                String string = value.toString();
                hashMap.put(o, StringUtils.removeUtf8Bom(string));
            }
            // 使用EasyExcel写入xlsx文件
            ExcelWriterBuilder writerBuilder = EasyExcel.write(xlsxFile);
            writerBuilder.sheet().doWrite(dataList);
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            try {
                excelReader.close();
            } catch (Exception e) {
                throw new RuntimeException(e);
            }

        }
    }


//    public void csvToExcel(InputStream inputStream, File excelFile) {
//        BufferedReader bufferedReader = null;
//        CSVReader reader = null;
//        FileReader fileReader = null;
//        InputStreamReader fileReader=null;
//        try {
//            fileReader = new InputStreamReader(inputStream, "UTF-8");
//            bufferedReader = new BufferedReader(fileReader);
//            Workbook workbook = new XSSFWorkbook(); // 创建一个Excel工作簿，使用.xlsx格式
//            Sheet sheet = workbook.createSheet("Sheet1"); // 创建一个工作表
//
//            String csvLine;
//            int rowNum = 0;
//            Scanner scanner = new Scanner(bufferedReader);
//            while (scanner.hasNextLine()) {
//                rowNum++;
//                Row row = sheet.createRow(rowNum - 1); // 行号从0开始
//                csvLine = scanner.nextLine();
//                String[] values = csvLine.split(","); // 假设CSV文件使用逗号分隔
//
//                int colNum = 0;
//                for (String value : values) {
//                    colNum++;
//                    Cell cell = row.createCell(colNum - 1); // 列号从0开始
//                    cell.setCellValue(StringUtils.removeUtf8Bom(value.trim())); // 去除可能的前后空格
//                }
//            }
//            // 写入到Excel文件
//            workbook.write(fileOut);
//            // 关闭workbook
//            workbook.close();
//        } catch (Exception e) {
//            e.printStackTrace();
//        } finally {
//            try {
//                bufferedReader.close();
//            } catch (Exception e) {
//                throw new RuntimeException(e);
//            }
//            try {
//                fileReader.close();
//            } catch (Exception e) {
//                throw new RuntimeException(e);
//            }
//            try {
//                reader.close();
//            } catch (Exception e) {
//                throw new RuntimeException(e);
//            }
//        }
//    }

    /**
     * 最基础导入
     *
     * @param inputStream
     * @param titleRows   表格标题行数,默认0
     * @param headerRows  表头行数,默认1
     * @param needVerify  是否需要检测excel
     * @param pojoClass   导入的对象
     * @return
     */
    private <T> List<T> baseImport(InputStream inputStream, Integer titleRows, Integer headerRows,
                                   boolean needVerify, Class<T> pojoClass) throws Exception {
        if (inputStream == null) {
            return null;
        } else {
            final ImportParams params = new ImportParams();
            params.setTitleRows(titleRows);
            params.setHeadRows(headerRows);
            params.setSaveUrl("/excel/");
            params.setNeedSave(true);
            params.setNeedVerify(needVerify);
            return ExcelImportUtil.importExcel(inputStream, pojoClass, params);
        }

    }

    /**
     * excel 导出
     *
     * @param list      数据列表
     * @param title     表格内数据标题
     * @param sheetName sheet名称
     * @param pojoClass pojo类型
     * @param fileName  导出时的excel名称
     * @param response
     */
    public void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response) throws IOException {
        ExportParams exportParams = new ExportParams(title, sheetName);
        exportParams.setCreateHeadRows(true);
        exportParams.setHeaderHeight(20);
        exportParams.setTitleHeight((short) 20);
        defaultExport(list, pojoClass, fileName, response, exportParams);
    }


    public void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, List<ExcelTitleVo> excelTitleVos, HttpServletResponse response) throws IOException {
        ExportParams exportParams = new ExportParams(title, sheetName);
        exportParams.setCreateHeadRows(true);
        exportParams.setHeaderHeight(20);
        exportParams.setTitleHeight((short) 20);
        defaultExport(list, pojoClass, fileName, response, exportParams, excelTitleVos);
    }

    public void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, FileOutputStream fileOutputStream) {
        ExportParams exportParams = new ExportParams(title, sheetName);
        exportParams.setCreateHeadRows(true);
        exportParams.setHeaderHeight(20);
        exportParams.setTitleHeight((short) 20);
        defaultExport(list, pojoClass, fileOutputStream, exportParams);
    }

    public Workbook exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass) {
        ExportParams exportParams = new ExportParams(title, sheetName);
        exportParams.setCreateHeadRows(true);
        exportParams.setHeaderHeight(20);
        exportParams.setTitleHeight((short) 20);
        Workbook sheets = defaultExport(list, pojoClass, exportParams);
        return sheets;
    }

    /**
     * excel 导出
     *
     * @param list         数据列表
     * @param pojoClass    pojo类型
     * @param fileName     导出时的excel名称
     * @param response
     * @param exportParams 导出参数（标题、sheet名称、是否创建表头，表格类型）
     */
    private void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) throws IOException {
        //把数据添加到excel表格中
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        downLoadExcel(fileName, response, workbook);
    }

    private void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams, List<ExcelTitleVo> excelTitleVos) throws IOException {
        //把数据添加到excel表格中
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        //获取标题头
        if (StringUtils.isNotNull(excelTitleVos)) {
            Sheet sheet = workbook.getSheet(fileName);
            Row sheetRow = sheet.getRow(1);
            short lastCellNum = sheetRow.getLastCellNum();
            for (int i = 0; i < lastCellNum; i++) {
                try {
                    Cell cell = sheetRow.getCell(i);
                    String stringCellValue = cell.getStringCellValue();
                    for (ExcelTitleVo excelTitleVo : excelTitleVos) {
                        String sourceName = excelTitleVo.getSourceName();
                        if (sourceName.equals(stringCellValue)) {
                            cell.setCellValue(excelTitleVo.getTargetName());
                        }
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }

        downLoadExcel(fileName, response, workbook);
    }


    private Workbook defaultExport(List<?> list, Class<?> pojoClass, ExportParams exportParams) {
        //把数据添加到excel表格中
        Workbook workbook = null;
        try {
            workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
            return workbook;
        } catch (Exception e) {
            return null;
        }
    }

    private void defaultExport(List<?> list, Class<?> pojoClass, FileOutputStream fileOutputStream, ExportParams exportParams) {
        //把数据添加到excel表格中
        Workbook workbook = null;
        try {
            workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
            workbook.write(fileOutputStream);
            workbook.close();
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            try {
                workbook.close();
            } catch (IOException e) {
                throw new RuntimeException(e);
            }
        }
    }

    /**
     * excel下载
     *
     * @param fileName 下载时的文件名称
     * @param response
     * @param workbook excel数据
     */
    private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) throws IOException {
        try {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".xls", "UTF-8"));
            workbook.write(response.getOutputStream());
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        } finally {
            workbook.close();
        }
    }


}
